﻿#region << 版 本 注 释 >>
/*----------------------------------------------------------------
 * 修改人：x
 * 时间：
 * 修改说明：
 *----------------------------------------------------------------*/
#endregion << 版 本 注 释 >>using System;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;
using System.Transactions;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using seejee_Backendium.Core.Models;
using seejee_Backendium.Data.Services;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory.Database;
using Microsoft.AspNetCore.Mvc;
using seejee_Backendium.Core.Models.DTO;

namespace seejee_Backendium.Data.Repositores
{
    public class OperatorRepository
    {
        private readonly string _connectionString;
        private readonly IConfiguration _configuration;

        public OperatorRepository(string connectionString, IConfiguration configuration)
        {
            _connectionString = connectionString;
            _configuration = configuration;
        }
        //获取操作员的token
        public string GetTokenByOperatorName(string cOperatorName)
        {
            string connStr = _configuration.GetConnectionString("Default");
            using (SqlConnection connection = new SqlConnection(connStr))
            {
                connection.Open();
                string query = "SELECT Token FROM SEBI_Operator WHERE cOperatorName = @cOperatorName";
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@cOperatorName", cOperatorName);
                    var result = command.ExecuteScalar();
                    return result?.ToString();
                }
            }
        }
        //将Token存入数据库
        public bool SaveToken(string cOperatorName, string tokenString)
        {
            string connStr = _configuration.GetConnectionString("Default");
            using (SqlConnection connection = new SqlConnection(connStr))
            {
                connection.Open();
                string updateQuery = "UPDATE SEBI_Operator SET Token = @Token WHERE cOperatorName = @cOperatorName";
                using (SqlCommand command = new SqlCommand(updateQuery, connection))
                {
                    command.Parameters.AddWithValue("@Token", tokenString);
                    command.Parameters.AddWithValue("@cOperatorName", cOperatorName);
                    int rowsAffected = command.ExecuteNonQuery();
                    return rowsAffected > 0; // 如果更新了至少一行，返回true
                }
            }
        }
        //操作员PC大屏登录对比密码
        public OperatorLoginResult ValidateOperator(string cOperatorName, string cOperatorPassword, bool forceLogin = false)
        {
            DataTable operatorTable = GetOperatorByOperatorname(cOperatorName);
            // 1. 检查用户是否存在
            if (operatorTable.Rows.Count == 0)
            {
                return new OperatorLoginResult
                {
                    IsSuccess = false,
                    IsAlreadyOnline = false,
                    Message = "用户不存在或被禁用"
                };
            }

            // 修正原代码中的语法错误（原代码此处有逻辑错误）
            if (operatorTable.Rows.Count != 1)
            {
                return new OperatorLoginResult
                {
                    IsSuccess = false,
                    IsAlreadyOnline = false,
                    Message = "数据异常，存在多个用户"
                };
            }
            // 2. 获取用户在线状态（处理可能的DBNull）
            bool isOnline = false;
            var onlineValue = operatorTable.Rows[0]["bOperatorOnLine"];
            if (onlineValue != DBNull.Value)
            {
                isOnline = Convert.ToBoolean(onlineValue);
            }
            // 3. 如果已在线且未确认强制登录，返回提示
            if (isOnline && !forceLogin)
            {
                return new OperatorLoginResult
                {
                    IsSuccess = false,
                    IsAlreadyOnline = true, // 标记为已在线
                    Message = "该用户已在线，是否继续登录？"
                };
            }
            string storedPassword = operatorTable.Rows[0]["cOperatorPassword"].ToString();
            string storedSalt = operatorTable.Rows[0]["Salt"].ToString();
            var finalHash = PasswordService.HashPassword(cOperatorPassword, storedSalt);
            bool isPasswordValid = string.Equals(finalHash, storedPassword, StringComparison.Ordinal);
            // 5. 密码验证成功，更新在线状态
            if (isPasswordValid)
            {
                UpdateOperatorOnlineStatus(cOperatorName, true);
                return new OperatorLoginResult
                {
                    IsSuccess = true,
                    IsAlreadyOnline = isOnline, // 告知前端是否是强制登录（原在线状态）
                    Message = isOnline ? "已强制登录" : "登录成功"
                };
            }

            // 6. 密码错误
            return new OperatorLoginResult
            {
                IsSuccess = false,
                IsAlreadyOnline = isOnline,
                Message = "密码错误"
            };
        }

        public DataTable GetOperatorByOperatorname(string cOperatorName)
        {
            string connStr = _configuration.GetConnectionString("Default");

            using (SqlConnection connection = new SqlConnection(connStr))
            {
                //connection.ConnectionString = "Server = 192.168.0.172; Database = seejee_Demo01; Operator ID = sa; Password = 123456; TrustServerCertificate = true; Connect Timeout = 30";
                connection.Open();
                string query = "SELECT cOperatorPassword,Salt,iOperatorPermissions,bOperatorOnLine FROM SEBI_Operator WHERE cOperatorName = @cOperatorName and iOperatorPermissions=0 and state =1";
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@cOperatorName", cOperatorName);
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    DataTable dataTable = new DataTable();
                    adapter.Fill(dataTable);
                    return dataTable;

                }
            }
        }
        //操作员BI看板登录对比密码
        public OperatorLoginResult ValidateBIOperator(string cOperatorName, string cOperatorPassword, bool forceLogin = false)
        {
            DataTable operatorTable = GetOperatorBIByOperatorname(cOperatorName);
            // 1. 检查用户是否存在
            if (operatorTable.Rows.Count == 0)
            {
                return new OperatorLoginResult
                {
                    IsSuccess = false,
                    IsAlreadyOnline = false,
                    Message = "用户不存在或被禁用"
                };
            }

            // 修正原代码中的语法错误（原代码此处有逻辑错误）
            if (operatorTable.Rows.Count != 1)
            {
                return new OperatorLoginResult
                {
                    IsSuccess = false,
                    IsAlreadyOnline = false,
                    Message = "数据异常，存在多个用户"
                };
            }
            // 2. 获取用户在线状态（处理可能的DBNull）
            bool isOnline = false;
            var onlineValue = operatorTable.Rows[0]["bOperatorOnLine"];
            if (onlineValue != DBNull.Value)
            {
                isOnline = Convert.ToBoolean(onlineValue);
            }
            // 3. 如果已在线且未确认强制登录，返回提示
            if (isOnline && !forceLogin)
            {
                return new OperatorLoginResult
                {
                    IsSuccess = false,
                    IsAlreadyOnline = true, // 标记为已在线
                    Message = "该用户已在线，是否继续登录？"
                };
            }
            string storedPassword = operatorTable.Rows[0]["cOperatorPassword"].ToString();
            string storedSalt = operatorTable.Rows[0]["Salt"].ToString();
            var finalHash = PasswordService.HashPassword(cOperatorPassword, storedSalt);
            bool isPasswordValid = string.Equals(finalHash, storedPassword, StringComparison.Ordinal);
            // 5. 密码验证成功，更新在线状态
            if (isPasswordValid)
            {
                UpdateOperatorOnlineStatus(cOperatorName, true);
                return new OperatorLoginResult
                {
                    IsSuccess = true,
                    IsAlreadyOnline = isOnline, // 告知前端是否是强制登录（原在线状态）
                    Message = isOnline ? "已强制登录" : "登录成功"
                };
            }

            // 6. 密码错误
            return new OperatorLoginResult
            {
                IsSuccess = false,
                IsAlreadyOnline = isOnline,
                Message = "密码错误"
            };
        }
        public DataTable GetOperatorBIByOperatorname(string cOperatorName)
        {
            string connStr = _configuration.GetConnectionString("Default");

            using (SqlConnection connection = new SqlConnection(connStr))
            {
                connection.Open();
                string query = "SELECT cOperatorPassword,Salt,iOperatorPermissions,bOperatorOnLine FROM SEBI_Operator WHERE cOperatorName = @cOperatorName and iOperatorPermissions=1 and state =1";
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@cOperatorName", cOperatorName);
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    DataTable dataTable = new DataTable();
                    adapter.Fill(dataTable);
                    return dataTable;

                }
            }
        }
        // 更新操作员在线状态
        public bool UpdateOperatorOnlineStatus(string cOperatorName, bool isOnline)
        {
            string connStr = _configuration.GetConnectionString("Default");
            using (SqlConnection connection = new SqlConnection(connStr))
            {
                connection.Open();
                // 更新bOperatorOnLine字段（假设字段类型为bit）
                string updateQuery = @"UPDATE SEBI_Operator 
                              SET bOperatorOnLine = @IsOnline
                              WHERE cOperatorName = @cOperatorName";

                using (SqlCommand command = new SqlCommand(updateQuery, connection))
                {
                    command.Parameters.AddWithValue("@IsOnline", isOnline);
                    command.Parameters.AddWithValue("@cOperatorName", cOperatorName);
                    command.ExecuteNonQuery(); // 执行更新
                }
                string updateSql = @"UPDATE SEBI_Operator SET LastActiveTime = @LastActiveTime WHERE cOperatorName = @cOperatorName";
                using (SqlCommand cmd = new SqlCommand(updateSql, connection))
                {
                    cmd.Parameters.AddWithValue("@LastActiveTime", DateTime.Now);
                    cmd.Parameters.AddWithValue("@cOperatorName", cOperatorName);
                    cmd.ExecuteNonQuery();
                }
            }
            return true;
        }
        public bool UpdateLastTime(string cOperatorName)
        {
            string connStr = _configuration.GetConnectionString("Default");
            using (SqlConnection connection = new SqlConnection(connStr))
            {
                connection.Open();
                string updateSql = @"UPDATE SEBI_Operator SET LastActiveTime = @LastActiveTime WHERE cOperatorName = @cOperatorName";
                using (SqlCommand cmd = new SqlCommand(updateSql, connection))
                {
                    cmd.Parameters.AddWithValue("@LastActiveTime", DateTime.Now);
                    cmd.Parameters.AddWithValue("@cOperatorName", cOperatorName);
                    cmd.ExecuteNonQuery();
                }
            }
            return true;
        }
        //获取所有操作员
        public PagedResult<OperatorInfoDTO> GetAllOperator(int pageIndex, int pageSize, string keyword)
        {
            var operators = new List<OperatorInfoDTO>();
            int totalCount = 0;
            string connStr = _configuration.GetConnectionString("Default");

            using (SqlConnection connection = new SqlConnection(connStr))
            {
                connection.Open();

                // 1. 修正总条数查询：使用新表SEBI_Operator和正确的查询条件
                string countSql = @"
            SELECT COUNT(*) 
            FROM SEBI_Operator
            WHERE 
                (cOperatorName = '' OR cOperatorName LIKE '%' + @keyword + '%') 
and iOperatorID !=1";

                using (SqlCommand countCmd = new SqlCommand(countSql, connection))
                {
                    countCmd.Parameters.AddWithValue("@keyword", keyword);
                    totalCount = (int)countCmd.ExecuteScalar();
                }

                // 2. 分页查询语句（使用提供的正确SQL）
                string query = @"
            select 
                iOperatorID,
                cOperatorName,
                cOperatorRealName,
                dOperatorCreateTime,
                iOperatorPermissions,
                bOperatorOnLine ,
                state
            from SEBI_Operator 
            where (cOperatorName = '' OR cOperatorName LIKE '%' + @keyword + '%')
and iOperatorID !=1
            ORDER BY cOperatorName 
            OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;;";

                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@Offset", (pageIndex - 1) * pageSize);
                    command.Parameters.AddWithValue("@PageSize", pageSize);
                    command.Parameters.AddWithValue("@keyword", keyword);

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            operators.Add(new OperatorInfoDTO
                            {
                                // 字段映射：严格按照SQL查询结果的列顺序和名称匹配
                                iOperatorID = reader.IsDBNull(0) ? 0 : reader.GetInt32(0),
                                cOperatorName = reader.IsDBNull(1) ? "" : reader.GetString(1),
                                cOperatorRealName = reader.IsDBNull(2) ? "" : reader.GetString(2),
                                dOperatorCreateTime = reader.GetDateTime(3), // 假设创建时间不为空，若可能为空需添加判断
                                iOperatorPermissions = reader.IsDBNull(4) ? 0 : reader.GetInt32(4),
                                // 新增：在线状态字段（可根据需要添加到DTO）
                                bOperatorOnLine = reader.IsDBNull(5) ? false : reader.GetBoolean(5),
                                state = reader.IsDBNull(6) ? false : reader.GetBoolean(6)
                            });
                            Console.WriteLine(operators);
                        }
                    }
                }
            }

            return new PagedResult<OperatorInfoDTO>
            {
                Items = operators,
                TotalCount = totalCount
            };
        }
        //修改密码
        public bool UpdatePassword(OperatorPasswordDTO operatorUpdatePassword)
        {
            string connStr = _configuration.GetConnectionString("Default");
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                // 开启事务 
                using (SqlTransaction transaction = conn.BeginTransaction())
                {
                    try
                    {
                        // 生成随机盐值 
                        var salt = PasswordService.GenerateSalt();
                        var finalHash = PasswordService.HashPassword(operatorUpdatePassword.cOperatorPassword, salt);
                        // 更新密码
                        string updatePasswordSql = @"UPDATE SEBI_Operator SET cOperatorPassword = @cOperatorPassword, Salt = @Salt  WHERE cOperatorName = @cOperatorName";
                        using (SqlCommand cmd = new SqlCommand(updatePasswordSql, conn, transaction))
                        {
                            cmd.Parameters.AddWithValue("@cOperatorName", operatorUpdatePassword.cOperatorName);
                            cmd.Parameters.AddWithValue("@cOperatorPassword", finalHash);
                            cmd.Parameters.AddWithValue("@Salt", salt);
                            int rowsAffected = cmd.ExecuteNonQuery();
                            if (rowsAffected == 0) throw new Exception("用户密码更新失败");
                        }
                        // 提交事务
                        transaction.Commit();
                        return true;
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }
        //修改旧密码,对比密码
        public bool UpdateOldPassword(string oldPassword, string newPassword, string cOperatorName)
        {
            DataTable operatorTable = GetOperatorBIByOperatorname(cOperatorName);

            if (operatorTable.Rows.Count == 0)
            {
                return false;
            }

            string storedPassword = operatorTable.Rows[0]["cOperatorPassword"].ToString();
            string storedSalt = operatorTable.Rows[0]["Salt"].ToString();
            var finalOldHash = PasswordService.HashPassword(oldPassword, storedSalt);
            bool isPasswordValid = string.Equals(finalOldHash, storedPassword, StringComparison.Ordinal);
            if (!isPasswordValid)
            {
                return false; // 旧密码不正确，返回false
            }
            // 旧密码正确，继续更新为新密码
            string connStr = _configuration.GetConnectionString("Default");
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                // 开启事务 
                using (SqlTransaction transaction = conn.BeginTransaction())
                {
                    try
                    {
                        // 生成随机盐值 
                        var salt = PasswordService.GenerateSalt();
                        var finalHash = PasswordService.HashPassword(newPassword, salt);
                        // 更新密码
                        string updatePasswordSql = @"UPDATE SEBI_Operator SET cOperatorPassword = @cOperatorPassword, Salt = @Salt  WHERE cOperatorName = @cOperatorName";
                        using (SqlCommand cmd = new SqlCommand(updatePasswordSql, conn, transaction))
                        {
                            cmd.Parameters.AddWithValue("@cOperatorName", cOperatorName);
                            cmd.Parameters.AddWithValue("@cOperatorPassword", finalHash);
                            cmd.Parameters.AddWithValue("@Salt", salt);
                            int rowsAffected = cmd.ExecuteNonQuery();
                            if (rowsAffected == 0) throw new Exception("用户密码更新失败");
                        }
                        // 提交事务
                        transaction.Commit();
                        return true;
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        //新增操作员
        public bool InsertOperator(OperatorInsertDTO users)
        {
            string connStr = _configuration.GetConnectionString("Default");
            using (SqlConnection conn = new SqlConnection(connStr))

            {
                conn.Open();
                // 开启事务 
                using (SqlTransaction transaction = conn.BeginTransaction())
                {
                    try
                    {
                        // 插入主表 ( SEBI_Operator)
                        string insertMainSql = @"INSERT INTO SEBI_Operator (cOperatorName,cOperatorPassword,cOperatorRealName, iOperatorPermissions,dOperatorCreateTime,Salt) VALUES (@cOperatorName, @cOperatorPassword,@cOperatorRealName, @iOperatorPermissions,@dOperatorCreateTime,@Salt)";
                        using (SqlCommand cmd = new SqlCommand(insertMainSql, conn, transaction))
                        {
                            cmd.Parameters.AddWithValue("@cOperatorName", users.cOperatorName ?? "");
                            //cmd.Parameters.AddWithValue("@cOperatorPassword", users.cOperatorPassword ?? "");
                            cmd.Parameters.AddWithValue("@cOperatorPassword", users.cOperatorPassword ?? "");
                            cmd.Parameters.AddWithValue("@cOperatorRealName", users.cOperatorRealName ?? "");
                            cmd.Parameters.AddWithValue("@iOperatorPermissions", users.iOperatorPermissions);
                            //cmd.Parameters.AddWithValue("@cOperatorOperatorKey", users.cOperatorUserKey ?? "");
                            //cmd.Parameters.AddWithValue("@dOperatorCreateTime", user.dOperatorCreateTime == default ? (object)DBNull.Value : user.dOperatorCreateTime.ToString("yyyy-MM-dd HH:mm:ss"));
                            cmd.Parameters.AddWithValue("@dOperatorCreateTime", users.dOperatorCreateTime == default ? (object)DBNull.Value : users.dOperatorCreateTime);
                            cmd.Parameters.AddWithValue("@Salt", users.Salt ?? "");
                            cmd.ExecuteNonQuery();
                        }

                        // 提交事务
                        transaction.Commit();
                        return true;
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        // 修改操作员信息
        public bool UpdateOperator(UpdateOperatorDTO user)
        {
            string connStr = _configuration.GetConnectionString("Default");

            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();

                // 开启事务
                using (SqlTransaction transaction = conn.BeginTransaction())
                {
                    try
                    {
                        // 更新操作员主表 (SEBI_Operator)
                        string updateMainSql = @"
                UPDATE SEBI_Operator
                SET cOperatorName = @cOperatorName,
                    cOperatorRealName = @cOperatorRealName,
                    iOperatorPermissions = @iOperatorPermissions
                WHERE iOperatorID = @iOperatorID";

                        using (SqlCommand cmd = new SqlCommand(updateMainSql, conn, transaction))
                        {
                            cmd.Parameters.AddWithValue("@iOperatorID", user.iOperatorID);
                            cmd.Parameters.AddWithValue("@cOperatorName", user.cOperatorName ?? "");
                            cmd.Parameters.AddWithValue("@cOperatorRealName", user.cOperatorRealName ?? "");
                            cmd.Parameters.AddWithValue("@iOperatorPermissions", user.iOperatorPermissions);

                            int mainRows = cmd.ExecuteNonQuery();
                            if (mainRows == 0)
                                throw new Exception("操作员表更新失败，未找到对应记录");
                        }

                        // 如果需要更新关联表，可以在这里添加相关逻辑
                        // 例如：更新操作员权限表或其他关联信息

                        // 提交事务
                        transaction.Commit();
                        return true;
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        // 删除操作员
        public bool DeleteOperator(string cOperatorName)
        {
            string connStr = _configuration.GetConnectionString("Default");
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlTransaction transaction = conn.BeginTransaction())
                {
                    try
                    {
                        // 如果有相关联的子表，先删除子表数据
                        // 例如：删除操作员的权限记录、操作日志等
                        // string deleteRelatedSql = "DELETE FROM OperatorLogs WHERE cOperatorName = @cOperatorName";
                        // using (SqlCommand cmd = new SqlCommand(deleteRelatedSql, conn, transaction))
                        // {
                        //     cmd.Parameters.AddWithValue("@cOperatorName", cOperatorName);
                        //     cmd.ExecuteNonQuery();
                        // }

                        // 再删除操作员主表数据
                        string deleteOperatorSql = "DELETE FROM SEBI_Operator WHERE cOperatorName = @cOperatorName";
                        using (SqlCommand cmd = new SqlCommand(deleteOperatorSql, conn, transaction))
                        {
                            cmd.Parameters.AddWithValue("@cOperatorName", cOperatorName);
                            int affectedRows = cmd.ExecuteNonQuery();
                            if (affectedRows == 0)
                                throw new Exception("未找到要删除的操作员");
                        }

                        transaction.Commit();
                        return true;
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }
        public bool UpdateStateAsync(int iOperatorID, bool state)
        {
            string connStr = _configuration.GetConnectionString("Default");
            using (SqlConnection connection = new SqlConnection(connStr))
            {
                connection.Open();
                // 更新bOperatorOnLine字段（假设字段类型为bit）
                string updateQuery = @"UPDATE SEBI_Operator 
                             SET state = @state        
                            WHERE iOperatorID = @iOperatorID";

                using (SqlCommand command = new SqlCommand(updateQuery, connection))
                {
                    command.Parameters.AddWithValue("@state", state);
                    command.Parameters.AddWithValue("@iOperatorID", iOperatorID);
                    command.ExecuteNonQuery(); // 执行更新
                }
            }
            return true;
        }

        // 保存Base64图片
        public bool SaveImage(string Base64Image, string cOperatorName)
        {
            string connStr = _configuration.GetConnectionString("Default");
            using (SqlConnection connection = new SqlConnection(connStr))
            {
                connection.Open();
                // 更新bOperatorOnLine字段（假设字段类型为bit）
                string updateQuery = @"UPDATE SEBI_Operator 
                              SET baseImage = @Base64Image
                              WHERE cOperatorName = @cOperatorName";

                using (SqlCommand command = new SqlCommand(updateQuery, connection))
                {
                    command.Parameters.AddWithValue("@Base64Image", Base64Image);
                    command.Parameters.AddWithValue("@cOperatorName", cOperatorName);
                    command.ExecuteNonQuery(); // 执行更新
                }
            }
            return true;
        }

        // 获取图片
        public string GetImage(string cOperatorName)
        {
            string connStr = _configuration.GetConnectionString("Default");
            using (SqlConnection connection = new SqlConnection(connStr))
            {
                connection.Open();
                string query = "SELECT baseImage FROM SEBI_Operator WHERE cOperatorName = @cOperatorName";
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@cOperatorName", cOperatorName);
                    var result = command.ExecuteScalar();
                    if (result != null && result != DBNull.Value)
                    {
                        return result.ToString();
                    }
                    else
                    {
                        return null; // 或者返回一个默认的Base64字符串
                    }
                }
            }
        }


    }
}
